

ALTER          FUNCTION fn_booking_by_sales_orders_for_projected_actual_report(
  
  @CUSTOMER_INPUT VARCHAR(100),
  @PRODUCT_INPUT VARCHAR(100),
  @MONTH_FROM INT,
  @MONTH_TO INT,
  @YEAR_INPUT INT )
  
  RETURNS @FN_RESULTS TABLE( CUSTOMER VARCHAR(100), PRODUCT VARCHAR(100), SALES_PERSON VARCHAR(100), END_USER VARCHAR(100), MONTH INT, YEAR INT, QUANTITY INT )
  
  AS
  BEGIN
  	

	DECLARE @MKTG_PART_NUMBER VARCHAR(100) 
 	
 	DECLARE @TEMP_CURSOR CURSOR 

	IF RTRIM(@MONTH_FROM) = -1
	BEGIN
		SET @MONTH_FROM = 0
	END

	IF RTRIM(@MONTH_TO) = -1
	BEGIN
		SET @MONTH_TO = 11
	END
	
	DECLARE @TO_MONTH INT	
	SET @TO_MONTH = @MONTH_FROM + @MONTH_TO 
	
  
  	IF @CUSTOMER_INPUT IS NULL OR RTRIM( @CUSTOMER_INPUT ) = ''
  	BEGIN
  		SET @CUSTOMER_INPUT = ''
  	END
  	

  	IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT = '0'
  	BEGIN
  		SET @TEMP_CURSOR = CURSOR FOR
  	    	SELECT C_NAME, PART_NUMBER, SO_SALES_PERSON, 
		--SO_ENDUSER,
		CASE
		WHEN ( SO.SO_ODM_ID IS NULL OR  SO.SO_ODM_ID < 0 ) THEN ''
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
		END as EndUser,
		COOL_QTY , DATEPART(MONTH,COOL_BOOKING_DATE)-1 MONTH, 
		DATEPART(YEAR,COOL_BOOKING_DATE) YEAR FROM CUSTOMER CUST, PRODUCT P, SALES_ORDER SO, CUSTOMER_OPEN_ORDER_LINEITEM COOL
	  	WHERE CUST.CUSTOMER_ID = SO.CUSTOMER_ID AND P.PRODUCT_ID = COOL.PRODUCT_ID AND SO.SALESORDER_ID = COOL.SALESORDER_ID and SO.SO_Is_Sample = 1
  		AND  ( ( DATEPART( MONTH, COOL.COOL_BOOKING_DATE ))-1 >= @MONTH_FROM AND DATEPART( YEAR,  COOL.COOL_BOOKING_DATE ) = @YEAR_INPUT AND ( DATEPART( MONTH, COOL.COOL_BOOKING_DATE ))-1 <= @TO_MONTH ) -- Modified by Gandhi on 14th Sep 2004
 	 		
  	END	
  	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT <> '0'
  	BEGIN
  			
		SET @TEMP_CURSOR = CURSOR FOR
  	    	SELECT C_NAME, PART_NUMBER, SO_SALES_PERSON, 
                --SO_ENDUSER,
		CASE
		WHEN ( SO.SO_ODM_ID IS NULL OR  SO.SO_ODM_ID < 0 ) THEN ''
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
		END as EndUser,
		COOL_QTY , DATEPART(MONTH,COOL_BOOKING_DATE)-1 MONTH, 
		DATEPART(YEAR,COOL_BOOKING_DATE) YEAR FROM CUSTOMER CUST, PRODUCT P, SALES_ORDER SO, CUSTOMER_OPEN_ORDER_LINEITEM COOL
	  	WHERE P.PART_NUMBER = @PRODUCT_INPUT 		
  		AND CUST.CUSTOMER_ID = SO.CUSTOMER_ID AND P.PRODUCT_ID = COOL.PRODUCT_ID AND SO.SALESORDER_ID = COOL.SALESORDER_ID and SO.SO_Is_Sample = 1
  		AND ( ( DATEPART( MONTH, COOL.COOL_BOOKING_DATE ))-1 >= @MONTH_FROM AND DATEPART( YEAR,  COOL.COOL_BOOKING_DATE ) = @YEAR_INPUT AND ( DATEPART( MONTH, COOL.COOL_BOOKING_DATE ))-1 <= @TO_MONTH )  -- Modified by Gandhi on 14th Sep 2004	
  	END
  	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT = '0'
  	BEGIN
  		SET @TEMP_CURSOR = CURSOR FOR
  	    	SELECT C_NAME, PART_NUMBER, SO_SALES_PERSON, 
		--SO_ENDUSER, 
		CASE
		WHEN ( SO.SO_ODM_ID IS NULL OR  SO.SO_ODM_ID < 0 ) THEN ''
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
		END as EndUser, 
		COOL_QTY , DATEPART(MONTH,COOL_BOOKING_DATE)-1 MONTH, 
		DATEPART(YEAR,COOL_BOOKING_DATE) YEAR FROM CUSTOMER CUST, PRODUCT P, SALES_ORDER SO, CUSTOMER_OPEN_ORDER_LINEITEM COOL
  		WHERE  CUST.C_NAME = @CUSTOMER_INPUT
  		AND CUST.CUSTOMER_ID = SO.CUSTOMER_ID AND P.PRODUCT_ID = COOL.PRODUCT_ID AND SO.SALESORDER_ID = COOL.SALESORDER_ID and SO.SO_Is_Sample = 1
  		AND ( ( DATEPART( MONTH, COOL.COOL_BOOKING_DATE ))-1 >= @MONTH_FROM AND DATEPART( YEAR,  COOL.COOL_BOOKING_DATE ) = @YEAR_INPUT AND ( DATEPART( MONTH, COOL.COOL_BOOKING_DATE ))-1 <= @TO_MONTH )  -- Modified by Gandhi on 14th Sep 2004
  	END
  	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT <> '0'
  	BEGIN
  		SET @TEMP_CURSOR = CURSOR FOR
  	    	SELECT C_NAME, PART_NUMBER, SO_SALES_PERSON, 
                --SO_ENDUSER,
		CASE
		WHEN ( SO.SO_ODM_ID IS NULL OR  SO.SO_ODM_ID < 0 ) THEN ''
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
		END as EndUser, 
		COOL_QTY , DATEPART(MONTH,COOL_BOOKING_DATE)-1 MONTH, 
		DATEPART(YEAR,COOL_BOOKING_DATE) YEAR FROM CUSTOMER CUST, PRODUCT P, SALES_ORDER SO, CUSTOMER_OPEN_ORDER_LINEITEM COOL
  		WHERE  CUST.C_NAME = @CUSTOMER_INPUT
  		AND P.PART_NUMBER = @PRODUCT_INPUT 		
  		AND CUST.CUSTOMER_ID = SO.CUSTOMER_ID AND P.PRODUCT_ID = COOL.PRODUCT_ID AND SO.SALESORDER_ID = COOL.SALESORDER_ID and SO.SO_Is_Sample = 1
  		AND ( ( DATEPART( MONTH, COOL.COOL_BOOKING_DATE ))-1 >= @MONTH_FROM AND DATEPART( YEAR,  COOL.COOL_BOOKING_DATE ) = @YEAR_INPUT AND ( DATEPART( MONTH, COOL.COOL_BOOKING_DATE ))-1 <= @TO_MONTH ) -- Modified by Gandhi on 14th Sep 2004
  	END
 	
 
  	DECLARE @CUSTOMER VARCHAR(100)
  	DECLARE @PRODUCT VARCHAR(100)
	DECLARE @SALES_PERSON VARCHAR(100)
	DECLARE @END_USER VARCHAR(100)
  	DECLARE @MONTH INT
  	DECLARE @YEAR INT
  	DECLARE @QUANTITY INT
  	DECLARE @OLD_QUANTITY INT
  	DECLARE @ROW_COUNT INT
  	
	
	  	OPEN @TEMP_CURSOR 
	  		FETCH NEXT FROM @TEMP_CURSOR
	  		INTO @CUSTOMER, @PRODUCT, @SALES_PERSON, @END_USER, @QUANTITY, @MONTH, @YEAR
	  		
	  		WHILE @@FETCH_STATUS = 0
	  		BEGIN
	  			SELECT @ROW_COUNT = COUNT(*) FROM @FN_RESULTS 
	  			WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER
	  				
	  			IF @ROW_COUNT = 0
	  			BEGIN
	  				INSERT INTO @FN_RESULTS VALUES ( @CUSTOMER, @PRODUCT, @SALES_PERSON, @END_USER, @MONTH, @YEAR, @QUANTITY)
	  			END
	  
	  			ELSE
	  			BEGIN
	  				SELECT @OLD_QUANTITY = QUANTITY FROM @FN_RESULTS
	  				WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER
	  
	  				UPDATE @FN_RESULTS SET QUANTITY = (@QUANTITY + @OLD_QUANTITY)
	  				WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER
	  			END	
			
				SELECT @MKTG_PART_NUMBER = Product.P_Marketing_part_Number from product,Process_Step  where Product.Part_Number = @PRODUCT and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID  
				UPDATE @FN_RESULTS SET PRODUCT =  @MKTG_PART_NUMBER WHERE PRODUCT = @PRODUCT		
	  			
	  			FETCH NEXT FROM @TEMP_CURSOR
	  			INTO  @CUSTOMER, @PRODUCT, @SALES_PERSON, @END_USER, @QUANTITY, @MONTH, @YEAR
	  		END

		CLOSE @TEMP_CURSOR
	  	DEALLOCATE @TEMP_CURSOR

	RETURN

  END

